﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Model;

namespace DAL.DataOperate
{
    public class BookDAL:DAL.App_Code.DBBase
    {
        public BookDAL()
            : base()
        {
        }
        #region 查询书
        public DataSet SelectAll()
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("select * from vi_Book where Book_Countint > 0 ");
            sqlStr.Append("and Book_Idint not in (select Book_Idint from tbSecondBook_Seller)");

            SqlParameter[] pa = { };
            return ExecuteString(sqlStr.ToString(), pa, "tbBook_Infor");
        }
        // 根据图书编号查找
        public DataSet SelectByBookId(int BookId)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("select * from vi_Book ");
            sqlStr.Append("where Book_Idint = ");
            sqlStr.Append("@Book_Idint ");

            SqlParameter[] pa = {
                   new SqlParameter("@Book_Idint",BookId)
                                };

            return ExecuteString(sqlStr.ToString(), pa, "tbBook_Infor");
        }
        // 根据购书日期 选择最新的8本
        public DataSet SelectNewBook()
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("select top(8) * from vi_Book where Book_Countint > 0 ");
            sqlStr.Append("order by Book_ImportTimedate DESC ");

            SqlParameter[] pa = { };

            return ExecuteString(sqlStr.ToString(), pa, "tbBook_Infor");
        }
        // 选择最热门的10本  销售最火爆的(及销量最多的）
        public DataSet SelectHotBook()
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("select top(8) * from vi_Book where Book_Countint > 0 order by Book_ImportCountint-Book_Countint DESC");

            SqlParameter[] pa = { };

            return ExecuteString(sqlStr.ToString(), pa, "tbBook_Infor");
        }
        // 选择推荐的10本
        public DataSet SelectRecommendedBook()
        {
            DataSet ds = new DataSet();
            return ds;
        }   
        // 根据小类别查找
        public DataSet SelectBySmallTypeBook(int SmallTypeId)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("select * from tbBook_Infor ");
            sqlStr.Append("where SmallType_Idint = @SmallType_Idint and Book_Countint > 0");

            SqlParameter[] pa = {
                new SqlParameter("@SmallType_Idint",SmallTypeId)
                                };

            return ExecuteString(sqlStr.ToString(), pa, "tbBook_Infor");
        }
        // 产品搜索
        public DataSet SearchBook(String BookName, String BookTypeName, float beginPrice, float endPrice)
        {
            SqlParameter[] pa = {
                new SqlParameter("@Book_Namestr",BookName),
                new SqlParameter("@BookTypeName",BookTypeName),
                new SqlParameter("@beginPrice",beginPrice),
                new SqlParameter("@endPrice",endPrice)
                                };

            return ExecuteProcedure("sp_SearchProduct", pa, "tbBook_Infor");
        }
        #endregion

        #region 增、删、改
        public bool InserBook(Model.Book book)
        {
            StringBuilder sqlStr = new StringBuilder();
            int smallTypeId = 0;
            int affectedRow = 0;
            int returnValue = 0;

            smallTypeId = GetSmallTypeId(book.SmallType_Namestr);
            //-------------------------------------------------------------
            SqlParameter[] pa2 = {
                new SqlParameter("@Book_Namestr",book.Book_Namestr),
                new SqlParameter("@SmallType_Idint",smallTypeId),
                new SqlParameter("@Book_Authorstr",book.Book_Authorstr),
                new SqlParameter("@Book_Publisherstr",book.Book_Publisherstr),
                new SqlParameter("@Book_Priceflo",book.Book_Priceflo),
                new SqlParameter("@Book_Discountflo",book.Book_Discountflo),
                new SqlParameter("@Book_Coverstr",book.Book_Coverstr),
                new SqlParameter("@Book_Descriptiontext",book.Book_Descriptiontext),
                new SqlParameter("@Book_Countint",book.Book_Countint),
                new SqlParameter("@Book_Statusbit",book.Book_Statusbit),
                new SqlParameter("@Book_ImportTimedate",book.Book_ImportTimedate),
                new SqlParameter("@Book_ImportCountint",book.Book_ImportCountint)
                            };

            returnValue = ExecuteRetProcedure("sp_InsertBook", pa2, out affectedRow);
            return returnValue > 0 ? true : false;
        }
        // 更新全部信息
        public bool UpdateBook(Model.Book book)
        {
            StringBuilder sqlStr = new StringBuilder();
            int smallTypeId = 0;
            int affectedRow = 0;
            int returnValue = 0;
            sqlStr.Append("select SmallType_Idint from tbBook_SmallType ");
            sqlStr.Append("where SmallType_Namestr = ");
            sqlStr.Append("@SmallType_Namestr");
            // 根据小类名称获得小类编号
            SqlParameter[] pa = {
                new SqlParameter("@SmallType_Namestr",book.SmallType_Namestr)
                                };
            DataSet ds = ExecuteString(sqlStr.ToString(), pa, "tbBook_SmallType");
            if ((ds == null) ||
               (ds.Tables.Count == 0) ||
               (ds.Tables.Count == 1 && ds.Tables[0].Rows.Count == 0))
            { return false; }
            else
            {
                smallTypeId = Int32.Parse(ds.Tables[0].Rows[0][0].ToString());
            }
            //-------------------------------------------------------------
            SqlParameter[] pa2 = {
                new SqlParameter("@Book_Idint",book.Book_Idint),
                new SqlParameter("@Book_Namestr",book.Book_Namestr),
                new SqlParameter("@SmallType_Idint",smallTypeId),
                new SqlParameter("@Book_Authorstr",book.Book_Authorstr),
                new SqlParameter("@Book_Publisherstr",book.Book_Publisherstr),
                new SqlParameter("@Book_Priceflo",book.Book_Priceflo),
                new SqlParameter("@Book_Discountflo",book.Book_Discountflo),
                new SqlParameter("@Book_Coverstr",book.Book_Coverstr),
                new SqlParameter("@Book_Descriptiontext",book.Book_Descriptiontext),
                new SqlParameter("@Book_Countint",book.Book_Countint),
                new SqlParameter("@Book_Statusbit",book.Book_Statusbit),
                new SqlParameter("@Book_ImportTimedate",book.Book_ImportTimedate),
                new SqlParameter("@Book_ImportCountint",book.Book_ImportCountint)
                            };

            returnValue = ExecuteRetProcedure("sp_UpdateBook", pa2, out affectedRow);
            return returnValue > 0 ? true : false;
        }
        // 更新图书数量
        public bool UpdateBook(int bookId,int buyCount)
        {
            int affectedRow = 0;
            int returnValue = 0;

            SqlParameter[] pa = {
                new SqlParameter("@Book_Idint",bookId),  // 图书编号
                new SqlParameter("@BuyCount",buyCount)   // 购买数量
                                };

            returnValue = ExecuteRetProcedure("sp_UpdateBookCount", pa,out affectedRow);
            return returnValue > 0 ? true : false;
        }
        public bool DeleteBook(int bookId)
        {
            StringBuilder sqlStr = new StringBuilder();
            int affectedRow = 0;

            sqlStr.Append("delete from vi_Book ");
            sqlStr.Append("where Book_Idint = ");
            sqlStr.Append("@Book_Idint");
            SqlParameter[] pa = {
                   new SqlParameter("@Book_Idint",bookId)
                                };
            ExecuteString(sqlStr.ToString(), pa, out affectedRow);
            return affectedRow > 0 ? true : false;
        }
        #endregion

        #region 图书类别操作

        #region 图书大类
        public DataSet SelectBigType()
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("select * from tbBook_BigType ");

            SqlParameter[] pa = { };
            return ExecuteString(sqlStr.ToString(), pa, "tbBook_BigType");
        }
        public DataSet SelectBigType(String BingTypeName)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("select * from tbBook_BigType ");
            sqlStr.Append("where BigType_Namestr = ");
            sqlStr.Append("@BigType_Namestr");

            SqlParameter[] pa = {
                new SqlParameter("@BigType_Namestr",BingTypeName)
                                };

            return ExecuteString(sqlStr.ToString(), pa, "tbBook_BigType");
        }
        public bool DeleteBig(int BigTypeId)
        {
            StringBuilder sqlStr = new StringBuilder();
            int affectedRow = 0;

            sqlStr.Append("delete from tbBook_BigType ");
            sqlStr.Append("where BigType_Idint = ");
            sqlStr.Append("@BigType_Idint");

            SqlParameter[] pa = {
                new SqlParameter("@BigType_Idint",BigTypeId)
                                };

            ExecuteString(sqlStr.ToString(), pa, out affectedRow);
            return affectedRow > 0 ? true : false;
        }
        public bool InsertBig(String BigTypeName)
        {
            StringBuilder sqlStr = new StringBuilder();
            int affectedRow = 0;
            int returnValue = 0;
            sqlStr.Append("insert into tbBook_BigType ");
            sqlStr.Append("values(@BigType_Namestr)");

            SqlParameter[] pa = {
                new SqlParameter("@BigType_Namestr",BigTypeName)
                                };

            returnValue = ExecuteRetString(sqlStr.ToString(), pa, out affectedRow);
            return returnValue > 0 ? true : false;
        }
        public bool UpdateBig(int BigTypeId, String BigTypeName)
        {
            StringBuilder sqlStr = new StringBuilder();
            int affectedRow = 0;
            int returnValue = 0;
            sqlStr.Append("update tbBook_BigType ");
            sqlStr.Append("set BigType_Namestr = @BigType_Namestr ");
            sqlStr.Append("where BigType_Idint = ");
            sqlStr.Append("@BigType_Idint");

            SqlParameter[] pa = {
                new SqlParameter("@BigType_Idint",BigTypeId)
                                };

            returnValue = ExecuteRetString(sqlStr.ToString(), pa, out affectedRow);
            return returnValue > 0 ? true : false;
        }
        #endregion

        #region 图书小类
        public DataSet SelectSmallType()
        {
            SqlParameter[] pa = { };
            return ExecuteProcedure("sp_SelectSmallTypeAll", pa, "tbBook_SmallType");
        }
        public DataSet SelectSmallType(String BigTypeName)
        {
            SqlParameter[] pa = {
                new SqlParameter("@BigType_Namestr",BigTypeName)
                                };

            return ExecuteProcedure("sp_SelectSmallType", pa, "tbBook_SmallType");
        }
        public DataSet SelectSmallType(int BigTypeId)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("select SmallType_Idint,SmallType_Namestr from tbBook_SmallType ");
            sqlStr.Append("where BigType_Idint = @BigType_Idint");

            SqlParameter[] pa = {
                new SqlParameter("@BigType_Idint",BigTypeId)
                                };

            return ExecuteString(sqlStr.ToString(), pa, "tbBook_SmallType");
        }
        public DataSet SelectSmallType(String BigTypeName, String SmallTypeName)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("SELECT SmallType_Idint FROM tbBook_SmallType ");
            sqlStr.Append("where SmallType_Namestr = @SmallType_Namestr ");
            sqlStr.Append("and BigType_Idint = ");
            sqlStr.Append("(select BigType_Idint from tbBook_BigType ");
            sqlStr.Append("where BigType_Namestr = @BigType_Namestr)");

            SqlParameter[] pa = {
                new SqlParameter("@SmallType_Namestr",SmallTypeName),
                new SqlParameter("@BigType_Namestr",BigTypeName)
                                };

            return ExecuteString(sqlStr.ToString(), pa, "tbBook_SmallType");
        }
        public bool DeleteSmall(int SmallTypeId)
        {
            StringBuilder sqlStr = new StringBuilder();
            int affectedRow = 0;

            sqlStr.Append("delete from tbBook_SmallType ");
            sqlStr.Append("where SmallType_Idint = ");
            sqlStr.Append("@SmallType_Idint");

            SqlParameter[] pa = {
                new SqlParameter("@SmallType_Idint",SmallTypeId)
                                };
            ExecuteString(sqlStr.ToString(), pa, out affectedRow);
            return affectedRow > 0 ? true : false;
        }
        public bool InsertSmall(String BigTypeName,String SmallTypeName)
        {
            StringBuilder sqlStr = new StringBuilder();
            int affectedRow = 0;
            int returnValue = 0;

            int BigTypeId = 0;   // 根据大类名称得到的大类编号
            DataSet ds = new DataSet();

            ds = SelectBigType(BigTypeName);
            if ((ds == null) ||
                 (ds.Tables.Count == 0) ||
                 (ds.Tables.Count == 1 && ds.Tables[0].Rows.Count == 0))
            {
                return false;
            }
            else
            {
                BigTypeId = Int32.Parse( ds.Tables[0].Rows[0]["BigType_Idint"].ToString());
                sqlStr.Append("insert into tbBook_SmallType ");
                sqlStr.Append("values(@BigType_Idint,@SmallType_Namestr)");

                SqlParameter[] pa = {
                    new SqlParameter("@BigType_Idint",BigTypeId),
                    new SqlParameter("@SmallType_Namestr",SmallTypeName)
                                    };

                returnValue = ExecuteRetString(sqlStr.ToString(), pa, out affectedRow);
                return returnValue > 0 ? true : false;
            }
            
        }
        public bool UpdateSmall(int SmallTypeId, String BigTypeName, String SmallTypeName)
        {
             StringBuilder sqlStr = new StringBuilder();
            int affectedRow = 0;
            int returnValue = 0;

            int BigTypeId = 0;   // 根据大类名称得到的大类编号
            DataSet ds = new DataSet();

            ds = SelectBigType(BigTypeName);
            if ((ds == null) ||
                 (ds.Tables.Count == 0) ||
                 (ds.Tables.Count == 1 && ds.Tables[0].Rows.Count == 0))
            {
                return false;
            }
            else
            {
                BigTypeId = Int32.Parse(ds.Tables[0].Rows[0]["BigType_Idint"].ToString());
                sqlStr.Append("update tbBook_SmallType ");
                sqlStr.Append("set BigType_Idint = @BigType_Idint, ");
                sqlStr.Append("SmallType_Namestr=@SmallType_Namestr ");
                sqlStr.Append("where SmallType_Idint = @SmallType_Idint ");

                SqlParameter[] pa = {
                    new SqlParameter("@SmallType_Idint",SmallTypeId),
                    new SqlParameter("@BigType_Idint",BigTypeId),
                    new SqlParameter("@SmallType_Namestr",SmallTypeName)
                                    };

                returnValue = ExecuteRetString(sqlStr.ToString(), pa, out affectedRow);
                return returnValue > 0 ? true : false;
            }
        }
        public int GetSmallTypeId(String SmallTypeName)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("select SmallType_Idint from tbBook_SmallType ");
            sqlStr.Append("where SmallType_Namestr = ");
            sqlStr.Append("@SmallType_Namestr");

            SqlParameter[] pa = {
                new SqlParameter("@SmallType_Namestr",SmallTypeName)
                                };

            DataSet ds = ExecuteString(sqlStr.ToString(), pa, "tbBook_SmallType");
            if ((ds == null) ||
               (ds.Tables.Count == 0) ||
               (ds.Tables.Count == 1 && ds.Tables[0].Rows.Count == 0))
            {
                return -1;
            }
            else
            {
                return Int32.Parse(ds.Tables[0].Rows[0][0].ToString());
            }
        }
        #endregion

        #endregion

        #region 二手书操作区
        public DataSet SelectSecondBookByUserId(int UserId)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("select * from vi_Book where Book_Idint = ");
            sqlStr.Append("(select Book_Idint from tbSecondBook_Seller ");
            sqlStr.Append("where Seller_Idint = @Seller_Idint)");

            SqlParameter[] pa = {
                new SqlParameter("@Seller_Idint",UserId)
                                };

            return ExecuteString(sqlStr.ToString(), pa, "tbBook_Infor");
        }
        public bool InsertSecondBook(Model.Book book, int UserId)
        {
            StringBuilder sqlStr = new StringBuilder();
            int smallTypeId = 0;
            int affectedRow = 0;
            int outputValue = 0;

            smallTypeId = GetSmallTypeId(book.SmallType_Namestr);

            //-------------------------------------------------------------
            SqlTransaction tran = Connection.BeginTransaction(IsolationLevel.ReadUncommitted);// 创建事物
            try
            {
                SqlParameter[] pa = {
                new SqlParameter("@Book_Namestr",book.Book_Namestr),
                new SqlParameter("@SmallType_Idint",smallTypeId),
                new SqlParameter("@Book_Authorstr",book.Book_Authorstr),
                new SqlParameter("@Book_Publisherstr",book.Book_Publisherstr),
                new SqlParameter("@Book_Priceflo",book.Book_Priceflo),
                new SqlParameter("@Book_Discountflo",book.Book_Discountflo),
                new SqlParameter("@Book_Coverstr",book.Book_Coverstr),
                new SqlParameter("@Book_Descriptiontext",book.Book_Descriptiontext),
                new SqlParameter("@Book_Countint",book.Book_Countint),
                new SqlParameter("@Book_Statusbit",book.Book_Statusbit),
                new SqlParameter("@Book_ImportTimedate",book.Book_ImportTimedate),
                new SqlParameter("@Book_ImportCountint",book.Book_ImportCountint)
                            };

                SqlCommand cmd = BuildOutCommand("sp_InsertSecondBook", pa, CommandType.StoredProcedure);
                cmd.Transaction = tran;                            // 事物开始
                affectedRow = cmd.ExecuteNonQuery();               // 影响的行数
                outputValue = (int)cmd.Parameters["@output"].Value;// 返回参数(订单号）

                if (affectedRow > 0 && outputValue > 0)
                {
                    SqlParameter[] pa2 ={
                      new SqlParameter("@Book_Idint",outputValue),
                      new SqlParameter("@Seller_Idint",UserId)
                                        };

                    SqlCommand cmd2 = BuildRetCommand("sp_InsertSecondBook_Seller", pa2, CommandType.StoredProcedure);
                    cmd2.Transaction = tran;
                    affectedRow = cmd2.ExecuteNonQuery();// 影响的行数
                }
            }
            catch
            {
                tran.Rollback();
                return false;
            }
            if (affectedRow > 0)
            {
                tran.Commit();
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion
    }
}
